package org.neframework.webtest.service;

import java.util.List;
import java.util.Map;

import org.neframework.webtest.model.t_s_customer;

import com.tmsps.neframework.core.common.page.Page;
import com.tmsps.neframework.core.log.NeLog;
import com.tmsps.neframework.core.utils.ChkTools;
import com.tmsps.neframework.jpa.base.BaseService;
import com.tmsps.neframework.jpa.sql.param.NeParamList;
import com.tmsps.neframework.jpa.utils.SqlHelperTools;

public class CustomerService extends BaseService {

	public static void main(String[] args) {
		CustomerService s = new CustomerService();
		System.err.println(SqlHelperTools.getModelAllFileds(t_s_customer.class, "t"));
		NeLog.info(s.selectCustomerList(null, null, null, new Page()));
	}

	public List<Map<String, Object>> selectCustomerList(String srh_name, String srh_bank_id, Map<String, String> sort_params, Page page) {
		// TODO 查询客户列表
		String sql = "select t.kid,t.status,t.created,t.name,t.sex,t.mobile,t.address,t.telephone,t.animal,t.education,t.age,t.ercode,t.ercode_addr,t.address_old,t.email,t.qs_name,t.qs_relation,t.qs_mobile,t.qs_other_name,t.qs_other_relation,t.qs_other_mobile,t.company,t.company_position,t.company_telephone,t.company_depart,t.company_address,"
				+ " count(tc.kid) card_cnt from t_s_customer t "
				+ " left outer join t_s_customer_card tc on tc.status=0 and t.kid=tc.customer_id and (tc.bank_id like ?) "
				+ " where t.status=0 and (t.name like ?)  "
				+ " group by t.kid,t.status,t.created,t.name,t.sex,t.mobile,t.address,t.telephone,t.animal,t.education,t.age,t.ercode,t.ercode_addr,t.address_old,t.email,t.qs_name,t.qs_relation,t.qs_mobile,t.qs_other_name,t.qs_other_relation,t.qs_other_mobile,t.company,t.company_position,t.company_telephone,t.company_depart,t.company_address ";

		String sql_count = "select count(*) cnt from t_s_customer t " + " left outer join t_s_customer_card tc on tc.status=0 and t.kid=tc.customer_id and (tc.bank_id like ?) "
				+ " where t.status=0 and (t.name like ?) ";

		if (ChkTools.isNull(srh_bank_id)) {
			srh_bank_id = "";
		}
		if (ChkTools.isNull(srh_name)) {
			srh_name = "";
		}

		List<Map<String, Object>> list = jc.queryForList(sql, sql_count, new String[] { "%" + srh_bank_id + "%", "%" + srh_name + "%" }, sort_params, page);
		
		return list;
	}

	public List<Map<String, Object>> selectCustomerCardList(String customer_id, Map<String, String> sort_params, Page page) {
		// TODO 查找客户的信用卡
		String sql = "select t.*,tb.name bank_name from t_s_customer_card t " + " left outer join t_s_bank tb on t.bank_id=tb.kid " + " where t.status=0 and t.customer_id=?";
		NeParamList param = NeParamList.makeParams();
		param.add(customer_id);
		List<Map<String, Object>> list = jcn.queryForList(sql, param, sort_params, page);

		return list;
	}

	public List<Map<String, Object>> selectCustomerCardPosList(String customer_card_id, Map<String, String> sort_params, Page page) {
		// TODO 查询刷卡记录
		String sql = "select * from t_s_customer_card_pos t where t.status=0 and (t.customer_card_id=?) ";
		NeParamList params = NeParamList.makeParams();
		params.add(customer_card_id);

		List<Map<String, Object>> list = jcn.queryForList(sql, params, sort_params, page);

		return list;
	}

	public List<Map<String, Object>> selectCardList(String srh_name, String srh_card_no, String srh_bill_day, String srh_bill_back_day, Map<String, String> sort_params, Page page) {

		if (ChkTools.isNotNull((sort_params.get("bank_name")))) {
			sort_params.put("table", "");
		}
		// TODO 查询所有信用卡
		String sql = "select t.*,tcust.name customer_name,tb.name bank_name from t_s_customer_card t " + " left outer join t_s_bank tb on t.bank_id=tb.kid "
				+ " left outer join t_s_customer tcust on t.customer_id=tcust.kid " + " where t.status=0 and (tcust.name like ?) and (t.card_no like ?) and (t.bill_day=?) and (t.bill_back_day=?) ";
		NeParamList params = NeParamList.makeParams();
		params.addLike(srh_name);
		params.addLike(srh_card_no);
		params.add(srh_bill_day);
		params.add(srh_bill_back_day);

		List<Map<String, Object>> list = jcn.queryForList(sql, params, sort_params, page);

		return list;
	}
}
